#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
from shop_conn import *
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

def get_cate_data(date):
    
    print "get_cate_data bigin : %s"%date
    
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    prod_conn = set_product_conn()
    shop_conn = set_shop_conn()    
    stat_table = "cate_city_metrics"
    
    #product [143,515,1006] ->shop[899,901,905]
    dct ={}
    query = "select a.cateid,b.cateid from t_category a left join t_category b on replace(a.catename,'/','')=replace(b.catename,'/','') where  substring_index(b.fullpath,',',1)=895 and b.depth=2"
    items = stat_conn.get_results(query)
    for a_cid,b_cid in items:
        dct["%s"%a_cid]=b_cid
        #print dct["%s"%a_cid]
        
    shtypes=[0,1]
    
    query_cate = "select pid,cateid,catename from t_category where pid in (143,515,1006) " #3 ge feilei
    cates = stat_conn.get_results(query_cate)
    for cate in cates:
        new_shop_count = 0
        online_shop_count = 0
        new_product_count = 0
        online_product_count =0
        
        pay_amount = 0.0
        pay_order_count = 0
        selled_shop_count = 0
        shop_type = 0
    
        pid = cate[0]
        cate_id = cate[1]
        cate_name = cate[2]
        #print cate_name
        key = "%s"%cate_id
        #print "test"
        shop_cate_id = None
        if dct.has_key(key):
            shop_cate_id = dct[key]

        #print "test"
        query_in = " select catename from t_category where cateid=%s"%pid
        items_in = stat_conn.get_results(query_in)
        pcate_name = items_in[0][0]
        
        for shop_type in shtypes:
            pv  = 0
            visits = 0
            new_visits  = 0
            uv = 0
            new_uv = 0
            #print uv
            query_in = " select ifnull(sum(pv),0),ifnull(sum(visits),0),ifnull(sum(new_visits),0),ifnull(sum(uv),0),ifnull(sum(new_uv),0) "
            query_in += " from product_info_visit_stats "
            query_in += " where cate_id=%s and stat_date='%s' and shop_type=%s"%(cate_id,date,shop_type)
            items_in = stat_conn.get_results(query_in)
            #print len(items_in)
            if len(items_in) > 0:
                pv  = items_in[0][0]
                visits  = items_in[0][1]
                new_visits  = items_in[0][2]
                uv  = items_in[0][3]
                new_uv = items_in[0][4]
                    
            #print uv            
            if shop_cate_id != None:
                query_sh = "select count(1) from shop where date(addTime)='%s' and cateid=%s and freeOrPay=%s"%(date,shop_cate_id,shop_type)
                items_sh = shop_conn.get_results(query_sh)
                new_shop_count = items_sh[0][0]
                
                query_sh = "select count(1) from shop where state=1 and cateid=%s and freeOrPay=%s"%(shop_cate_id,shop_type)
                items_sh = shop_conn.get_results(query_sh)
                online_shop_count = items_sh[0][0]
                
                #query_pr = "select count(1) from product_property where stat_date='%s' and cate_id=%s and shop_type=%s"%(date,cate_id,shop_type)
                #items_pr = shop_conn.get_results(query_pr)
                #new_shop_count = items_pr[0][0]
                
            query_od = " select count(1) ,ifnull(sum(pay_amount),0) from ( "
            query_od += " select order_id,pay_amount "
            query_od += " from order_action "
            query_od += " where date(create_time)='%s' and order_state in (3, 4, 5, 8) and cate_id=%s and shop_type=%s "%(date,cate_id,shop_type)
            query_od += " group by order_id) a "
            #print query_od
            items_od = stat_conn.get_results(query_od)
            if len(items_od) > 0:
                pay_order_count = items_od[0][0]
                pay_amount = items_od[0][1]
                #selled_shop_count = items_od[0][2]
                    
            query_od = "select count(distinct shop_id) "
            query_od += " from order_action "
            query_od += " where date(create_time)='%s' and order_state in (3, 4, 5, 8) and cate_id=%s and shop_type=%s "%(date,cate_id,shop_type)
            items_od = stat_conn.get_results(query_od)
            if len(items_od) > 0:
                selled_shop_count = items_od[0][0]
            
            #print uv
            param = [date,pid,pcate_name,cate_id,cate_name,pv,visits,uv,new_visits,new_uv,online_shop_count,new_shop_count,pay_order_count,pay_amount,selled_shop_count,shop_type]
            stat_conn.insert_record(stat_table,param)
            

    stat_conn.close();
    shop_conn.close()
    prod_conn.close()
    conn.close()
    print "get_cate_data end : %s"%date
    
    
#logic
def execute(date):
    get_cate_data(date)
    try:
        pass
        #get_cate_data(date)
    except Exception,e:
        print 'get_cate_data:',e
#

def main():

    #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        execute( stat_date) 
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)

if __name__=='__main__':
    main()